﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Entity;
using IRepository;

namespace Repository
{
    public class InventoryRepository : IInventoryRepository
    {
        private readonly IDbConnection _dbConnection;

        public InventoryRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        // 库存主表操作
        public async Task<List<InventoryEntity>> GetByWarehouseAndProductAsync(int warehouseId, string productCode)
        {
            var sql = "SELECT * FROM wms_knzy_inventory WHERE warehouse_id = @WarehouseId AND product_code = @ProductCode";
            return (await _dbConnection.QueryAsync<InventoryEntity>(sql, new { WarehouseId = warehouseId, ProductCode = productCode })).AsList();
        }

        public async Task<List<InventoryEntity>> GetByWarehouseAndProductAsync(int warehouseId, string productCode, IDbTransaction transaction)
        {
            var sql = "SELECT * FROM wms_knzy_inventory WHERE warehouse_id = @WarehouseId AND product_code = @ProductCode";
            return (await _dbConnection.QueryAsync<InventoryEntity>(sql, new { WarehouseId = warehouseId, ProductCode = productCode }, transaction)).AsList();
        }

        public async Task UpdateAsync(InventoryEntity entity)
        {
            var sql = @"UPDATE wms_knzy_inventory SET
                            warehouse_code = @warehouse_code,
                            location = @location,
                            product_code = @product_code,
                            current_inventory = @current_inventory,
                            planned_outgoing_quantity = @planned_outgoing_quantity
                        WHERE inventory_id = @inventory_id";
            await _dbConnection.ExecuteAsync(sql, entity);
        }

        public async Task UpdateAsync(InventoryEntity entity, IDbTransaction transaction)
        {
            var sql = @"UPDATE wms_knzy_inventory SET
                            warehouse_code = @warehouse_code,
                            location = @location,
                            product_code = @product_code,
                            current_inventory = @current_inventory,
                            planned_outgoing_quantity = @planned_outgoing_quantity
                        WHERE inventory_id = @inventory_id";
            await _dbConnection.ExecuteAsync(sql, entity, transaction);
        }

        public async Task AddAsync(InventoryEntity entity)
        {
            var sql = @"INSERT INTO wms_knzy_inventory (
                            warehouse_id, warehouse_code, location,
                            product_code, current_inventory, planned_outgoing_quantity
                        ) VALUES (
                            @warehouse_id, @warehouse_code, @location,
                            @product_code, @current_inventory, @planned_outgoing_quantity
                        );
                        SELECT CAST(SCOPE_IDENTITY() AS INT);";
            entity.inventory_id = await _dbConnection.QueryFirstAsync<int>(sql, entity);
        }

        public async Task AddAsync(InventoryEntity entity, IDbTransaction transaction)
        {
            var sql = @"INSERT INTO wms_knzy_inventory (
                            warehouse_id, warehouse_code, location,
                            product_code, current_inventory, planned_outgoing_quantity
                        ) VALUES (
                            @warehouse_id, @warehouse_code, @location,
                            @product_code, @current_inventory, @planned_outgoing_quantity
                        );
                        SELECT CAST(SCOPE_IDENTITY() AS INT);";
            entity.inventory_id = await _dbConnection.QueryFirstAsync<int>(sql, entity, transaction);
        }

        public async Task LockWarehouseAsync(string warehouseName)
        {
            var sql = @"UPDATE wms_knzy_warehouse 
                       SET warehouse_locked = 1, 
                           update_time = GETDATE()
                       WHERE warehouse_name = @WarehouseName";

            var result = await _dbConnection.ExecuteAsync(sql, new { WarehouseName = warehouseName });
            if (result == 0)
                throw new Exception($"仓库 '{warehouseName}' 不存在或锁定失败");
        }

        // 盘点明细操作
        public async Task AddInventoryDetailsAsync(int listId, List<InventoryDetailsEntity> details)
        {
            var sql = @"INSERT INTO wms_knzy_inventory_details (
                            inventory_list_id, product_code, book_inventory, book_amount,
                            inventory_location, stkt_num, pl_num, reference_unit_price,
                            profit_and_loss_amount, note, create_by, create_time
                        ) VALUES (
                            @inventory_list_id, @product_code, @book_inventory, @book_amount,
                            @inventory_location, @stkt_num, @pl_num, @reference_unit_price,
                            @profit_and_loss_amount, @note, @create_by, GETDATE()
                        )";

            foreach (var detail in details)
            {
                detail.inventory_list_id = listId;
                await _dbConnection.ExecuteAsync(sql, new
                {
                    inventory_list_id = detail.inventory_list_id,
                    product_code = detail.product_code,
                    book_inventory = detail.book_inventory,
                    book_amount = detail.book_amount,
                    inventory_location = detail.inventory_location,
                    stkt_num = detail.stkt_num,
                    pl_num = detail.pl_num,
                    reference_unit_price = detail.reference_unit_price,
                    profit_and_loss_amount = detail.profit_and_loss_amount,
                    note = detail.note,
                    create_by = detail.create_by
                });
            }
        }

        public async Task UpdateInventoryDetailAsync(InventoryDetailsEntity detail)
        {
            var sql = @"UPDATE wms_knzy_inventory_details SET
                            product_code = @product_code,
                            book_inventory = @book_inventory,
                            book_amount = @book_amount,
                            inventory_location = @inventory_location,
                            stkt_num = @stkt_num,
                            pl_num = @pl_num,
                            reference_unit_price = @reference_unit_price,
                            profit_and_loss_amount = @profit_and_loss_amount,
                            note = @note,
                            update_by = @update_by,
                            update_time = GETDATE()
                        WHERE inventory_detail_id = @inventory_detail_id";

            await _dbConnection.ExecuteAsync(sql, new
            {
                product_code = detail.product_code,
                book_inventory = detail.book_inventory,
                book_amount = detail.book_amount,
                inventory_location = detail.inventory_location,
                stkt_num = detail.stkt_num,
                pl_num = detail.pl_num,
                reference_unit_price = detail.reference_unit_price,
                profit_and_loss_amount = detail.profit_and_loss_amount,
                note = detail.note,
                update_by = detail.update_by,
                inventory_detail_id = detail.inventory_detail_id
            });
        }

        public async Task<List<InventoryDetailsEntity>> GetInventoryDetailsByListIdAsync(int listId)
        {
            var sql = @"SELECT 
                            inventory_detail_id,
                            inventory_list_id,
                            product_code,
                            book_inventory,
                            book_amount,
                            inventory_location,
                            stkt_num,
                            pl_num,
                            reference_unit_price,
                            profit_and_loss_amount,
                            note
                        FROM wms_knzy_inventory_details 
                        WHERE inventory_list_id = @ListId AND is_delete = 0";

            return (await _dbConnection.QueryAsync<InventoryDetailsEntity>(sql, new { ListId = listId })).AsList();
        }

        public async Task DeleteInventoryDetailAsync(int detailId)
        {
            var sql = @"UPDATE wms_knzy_inventory_details 
                       SET is_delete = 1, 
                           update_time = GETDATE()
                       WHERE inventory_detail_id = @DetailId";
            await _dbConnection.ExecuteAsync(sql, new { DetailId = detailId });
        }

        public async Task<bool> CheckWarehouseLockAsync(string warehouseName)
        {
            try
            {
                var sql = @"SELECT warehouse_status 
                   FROM wms_basic_warehouse 
                   WHERE warehouse_name = @WarehouseName AND is_delete = 0";

                var status = await _dbConnection.QueryFirstOrDefaultAsync<string>(
                    sql,
                    new { WarehouseName = warehouseName }
                );

                Console.WriteLine($"仓库 '{warehouseName}' 状态: {status}, 锁定: {status == "2"}");
                return status == "2";
            }
            catch (Exception ex)
            {
                Console.WriteLine($"检查仓库锁定状态异常: {ex.Message}");
                return false;
            }
        }

        public async Task<Dictionary<string, int>> GetBatchStockByProductCodesAsync(
            List<string> productCodes,
            int? warehouseId = null,
            string location = null)
        {
            Console.WriteLine($"🔍 库存汇总查询 - 产品: {string.Join(",", productCodes)}, 仓库ID: {warehouseId}, 库位: {location}");

            if (!productCodes.Any())
                return new Dictionary<string, int>();

            var sqlBuilder = new StringBuilder(@"
        SELECT 
            product_code, 
            SUM(current_inventory) AS total_stock 
        FROM wms_knzy_inventory 
        WHERE product_code IN @ProductCodes 
          AND is_delete = 0");

            var parameters = new DynamicParameters();
            parameters.Add("ProductCodes", productCodes);

            if (warehouseId.HasValue)
            {
                sqlBuilder.Append(" AND warehouse_id = @WarehouseId");
                parameters.Add("WarehouseId", warehouseId.Value);
                Console.WriteLine($"✅ 按指定仓库查询: {warehouseId.Value}");
            }

            if (!string.IsNullOrEmpty(location))
            {
                sqlBuilder.Append(" AND location = @Location");
                parameters.Add("Location", location);
                Console.WriteLine($"✅ 按指定库位查询: {location}");
            }

            sqlBuilder.Append(" GROUP BY product_code");

            var sql = sqlBuilder.ToString();
            Console.WriteLine($"📝 执行SQL: {sql}");

            var result = await _dbConnection.QueryAsync<(string product_code, int total_stock)>(
                sql,
                parameters);

            var stockDict = result.ToDictionary(
                item => item.product_code,
                item => item.total_stock
            );

            // 补充：未查询到的产品，库存设为0
            foreach (var code in productCodes)
            {
                if (!stockDict.ContainsKey(code))
                    stockDict[code] = 0;
            }

            return stockDict;
        }

        public async Task<InventoryEntity> GetByProductCodeAndWarehouseIdAsync(string productCode, int warehouseId, IDbTransaction transaction)
        {
            var sql = "SELECT * FROM wms_knzy_inventory WHERE product_code = @ProductCode AND warehouse_id = @WarehouseId AND is_delete = 0";
            return await _dbConnection.QueryFirstOrDefaultAsync<InventoryEntity>(sql, new { ProductCode = productCode, WarehouseId = warehouseId }, transaction);
        }

        public async Task UpdateInventoryAsync(InventoryEntity entity, IDbTransaction transaction)
        {
            var sql = "UPDATE wms_knzy_inventory SET current_inventory = @current_inventory WHERE inventory_id = @inventory_id";
            await _dbConnection.ExecuteAsync(sql, entity, transaction);
        }

        public async Task AddInventoryAsync(InventoryEntity entity, IDbTransaction transaction)
        {
            var sql = @"INSERT INTO wms_knzy_inventory (
                    warehouse_id, warehouse_code, product_code, current_inventory, 
                    planned_outgoing_quantity, location, location_id, is_delete
                ) VALUES (
                    @warehouse_id, @warehouse_code, @product_code, @current_inventory, 
                    @planned_outgoing_quantity, @location, @location_id, @is_delete
                )";
            await _dbConnection.ExecuteAsync(sql, entity, transaction);
        }
    }
}